{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Series:一维数组+行索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.1.5\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import json\n",
    "from pandas import json_normalize\n",
    "print(pd.__version__)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    1\n",
      "1    2\n",
      "2    3\n",
      "3    4\n",
      "dtype: int64\n",
      "3\n",
      "0    1\n",
      "1    2\n",
      "dtype: int64\n",
      "2    3\n",
      "3    4\n",
      "dtype: int64\n",
      "a    1\n",
      "b    2\n",
      "c    3\n",
      "d    4\n",
      "Name: demo, dtype: int64\n",
      "3\n"
     ]
    }
   ],
   "source": [
    "s = pd.Series([1, 2, 3, 4])\n",
    "print(s)\n",
    "print(s[2])\n",
    "print(s[:2])\n",
    "print(s[2:])\n",
    "\n",
    "s = pd.Series([1, 2, 3, 4], index=['a','b','c','d'],name='demo')\n",
    "print(s)\n",
    "print(s['c']) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[Index(['a', 'b', 'c', 'd'], dtype='object')]\n",
      "int64\n",
      "False\n",
      "1\n",
      "4\n",
      "[1 2 3 4]\n",
      "Index(['a', 'b', 'c', 'd'], dtype='object')\n",
      "a    1\n",
      "b    2\n",
      "c    3\n",
      "Name: demo, dtype: int64\n",
      "b    2\n",
      "c    3\n",
      "d    4\n",
      "Name: demo, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(s.axes)\n",
    "print(s.dtype)\n",
    "print(s.empty)\n",
    "print(s.ndim)  # 维度\n",
    "print(s.size)\n",
    "print(s.values)\n",
    "print(s.index)\n",
    "print(s.head(3)) \n",
    "print(s.tail(3))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0            c \n",
      "1        python\n",
      "2          java\n",
      "3            go\n",
      "4    javascript\n",
      "5            11\n",
      "6           NaN\n",
      "dtype: object\n",
      "0     2.0\n",
      "1     7.0\n",
      "2     4.0\n",
      "3     2.0\n",
      "4    10.0\n",
      "5     2.0\n",
      "6     NaN\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "s = pd.Series(['C ', ' Python', 'java', 'go','javascript','11',np.nan])\n",
    "print(s.str.lower())\n",
    "print(s.str.len())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    5\n",
      "1    5\n",
      "2    5\n",
      "3    5\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(pd.Series(5, index=[0, 1, 2, 3]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    1\n",
      "1    2\n",
      "2    3\n",
      "3    4\n",
      "dtype: int32\n"
     ]
    }
   ],
   "source": [
    "arr = np.array([1, 2, 3, 4])\n",
    "print(pd.Series(arr))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    0.502594\n",
      "1    1.119123\n",
      "2    1.423196\n",
      "3    0.006327\n",
      "4    0.290089\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print(pd.Series(np.random.randn(5)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "a    Google\n",
      "b       Run\n",
      "c      Wiki\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "dict = {'a': \"Google\", 'b': \"Run\", 'c': \"Wiki\"}\n",
    "print(pd.Series(dict))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DataFrame：表格  series组成的dict  行索引+列索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 222,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     Site  Age\n",
      "0  Google   10\n",
      "1  Runoob   12\n",
      "2    Wiki   13\n",
      "\n",
      "     Site  Age\n",
      "0  Google   10\n",
      "1  Runoob   12\n",
      "\n",
      "10\n",
      "\n",
      "12\n"
     ]
    }
   ],
   "source": [
    "data = {'Site':['Google', 'Runoob', 'Wiki'], 'Age':[10, 12, 13]}\n",
    "df = pd.DataFrame(data)\n",
    "print (df)\n",
    "print() \n",
    "print(df.loc[0:1])\n",
    "print()\n",
    "print(df.loc[0,'Age'])   \n",
    "print()\n",
    "print(df.iloc[1,1]) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Site   Age\n",
      "day1  Google  10.0\n",
      "day2  Runoob  12.0\n",
      "day3    Wiki  13.0\n",
      "\n",
      "        Site   Age\n",
      "day1  Google  10.0\n",
      "day2  Runoob  12.0\n",
      "\n",
      "10.0\n",
      "\n",
      "12.0\n",
      "\n",
      "       Age\n",
      "day1  10.0\n",
      "day2  12.0\n",
      "day3  13.0\n",
      "\n",
      "        Site   Age  Name\n",
      "day1  Google  10.0   NaN\n",
      "day2  Runoob  12.0   NaN\n",
      "day3    Wiki  13.0   NaN\n",
      "\n",
      "      Sub    Site   Age  Name\n",
      "day1    1  Google  10.0   NaN\n",
      "day2    2  Runoob  12.0   NaN\n",
      "day3    3    Wiki  13.0   NaN\n"
     ]
    }
   ],
   "source": [
    "data = [['Google',10],['Runoob',12],['Wiki',13]]\n",
    "df2 = pd.DataFrame(data,columns=['Site','Age'],index = [\"day1\", \"day2\", \"day3\"],dtype=float)\n",
    "print(df2)\n",
    "print()\n",
    "print(df2.loc['day1':'day2'])    # 切片\n",
    "print()\n",
    "print(df2.loc['day1','Age'])     # index+列名 \n",
    "print()\n",
    "print(df2.iloc[1,1])             # 行列index\n",
    "print()\n",
    "print(df2.filter(items=['Age']))\n",
    "print()\n",
    "df2['Name']=pd.Series([1,2,3])\n",
    "print(df2)\n",
    "print()\n",
    "df2.insert(0,column='Sub',value=[1,2,3])\n",
    "print(df2)\n",
    "del df2['Sub']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 排序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>20.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a   b     c     d\n",
       "0  1   2   NaN   NaN\n",
       "1  5  10  20.0  30.0\n",
       "2  5   5   NaN   NaN"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort_values('b')\n",
    "df.sort_values(['b', 'a'], ascending=[True, False])\n",
    "df.sort_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 遍历"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "index:a\n",
      "index:b\n",
      "index:c\n",
      "index:d\n"
     ]
    }
   ],
   "source": [
    "# 遍历列索引  index\n",
    "for col in df:\n",
    "    print(f'index:{col}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "key:a, value;0    1\n",
      "1    5\n",
      "2    5\n",
      "Name: a, dtype: int64\n",
      "key:b, value;0     2\n",
      "1    10\n",
      "2     5\n",
      "Name: b, dtype: int64\n",
      "key:c, value;0     NaN\n",
      "1    20.0\n",
      "2     NaN\n",
      "Name: c, dtype: float64\n",
      "key:d, value;0     NaN\n",
      "1    30.0\n",
      "2     NaN\n",
      "Name: d, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "for key, value in df.iteritems():\n",
    "    print(f'key:{key}, value;{value}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 a    1.0\n",
      "b    2.0\n",
      "c    NaN\n",
      "d    NaN\n",
      "Name: 0, dtype: float64\n",
      "1 a     5.0\n",
      "b    10.0\n",
      "c    20.0\n",
      "d    30.0\n",
      "Name: 1, dtype: float64\n",
      "2 a    5.0\n",
      "b    5.0\n",
      "c    NaN\n",
      "d    NaN\n",
      "Name: 2, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "for row_index, row in df.iterrows():\n",
    "    print(row_index, row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Pandas(Index=0, a=1, b=2, c=nan, d=nan)\n",
      "Pandas(Index=1, a=5, b=10, c=20.0, d=30.0)\n",
      "Pandas(Index=2, a=5, b=5, c=nan, d=nan)\n"
     ]
    }
   ],
   "source": [
    "for row in df.itertuples():\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 分组聚合"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<pandas.core.groupby.generic.DataFrameGroupBy object at 0x00000185FF1B52E8>\n"
     ]
    }
   ],
   "source": [
    "df.groupby('a')\n",
    "df.aggregate('a')\n",
    "df.pivot_table(values='value', index='index_column', columns='column_name', aggfunc='function_name')\n",
    "\n",
    "# 将多个数据框按照行或列进行合并\n",
    "df = pd.concat([df1, df2])\n",
    "\n",
    "# 按照指定列将两个数据框进行合并\n",
    "df = pd.merge(df1, df2, on='column_name')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 234,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  Name   Age  Rating\n",
      "0   小明  25.0     5.0\n",
      "1   小亮  34.0     4.0\n",
      "2   小红  24.0     3.0\n",
      "3   小华   NaN     NaN\n",
      "\n",
      "sum:Series([], dtype: float64)\n",
      "\n",
      "sum:0    30.0\n",
      "1    38.0\n",
      "2    27.0\n",
      "3     0.0\n",
      "dtype: float64\n",
      "\n",
      "mean:Age       27.666667\n",
      "Rating     4.000000\n",
      "dtype: float64\n",
      "\n",
      "median:Age       25.0\n",
      "Rating     4.0\n",
      "dtype: float64\n",
      "\n",
      "std:Age       5.507571\n",
      "Rating    1.000000\n",
      "dtype: float64\n",
      "\n",
      "min:Name      小亮\n",
      "Age       24\n",
      "Rating     3\n",
      "dtype: object\n",
      "\n",
      "max:Name      小红\n",
      "Age       34\n",
      "Rating     5\n",
      "dtype: object\n",
      "\n",
      "describe:             Age  Rating\n",
      "count   3.000000     3.0\n",
      "mean   27.666667     4.0\n",
      "std     5.507571     1.0\n",
      "min    24.000000     3.0\n",
      "25%    24.500000     3.5\n",
      "50%    25.000000     4.0\n",
      "75%    29.500000     4.5\n",
      "max    34.000000     5.0\n"
     ]
    }
   ],
   "source": [
    "data = {'Name': pd.Series(['小明', '小亮', '小红', '小华']),\n",
    "        'Age': pd.Series([25, 34, 24]),\n",
    "        'Rating': pd.Series([5.00, 4.0, 3.00])}\n",
    "df5 = pd.DataFrame(data)\n",
    "print(df5)\n",
    "print()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "name = [\"Google\", \"Runoob\", \"Taobao\", \"Wiki\", \"Q\", \"Di\"]\n",
    "site = [\"www.google.com\", \"www.runoob.com\", \"www.taobao.com\", \"www.wikipedia.org\", \"Q\", \"D\"]\n",
    "age = [90, 40, 80, 98,80, 98]\n",
    "   \n",
    "\n",
    "dict = {'name': name, 'site': site, 'age': age}  \n",
    "df = pd.DataFrame(dict)\n",
    "df.to_csv('site.csv',sep='|')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Unnamed: 0    name               site  age\n",
      "0           0  Google     www.google.com   90\n",
      "1           1  Runoob     www.runoob.com   40\n",
      "2           2  Taobao     www.taobao.com   80\n",
      "3           3    Wiki  www.wikipedia.org   98\n",
      "4           4       Q                  Q   80\n",
      "5           5      Di                  D   98\n",
      "   Unnamed: 0    name               site  age\n",
      "0           0  Google     www.google.com   90\n",
      "1           1  Runoob     www.runoob.com   40\n",
      "2           2  Taobao     www.taobao.com   80\n",
      "3           3    Wiki  www.wikipedia.org   98\n",
      "4           4       Q                  Q   80\n",
      "5           5      Di                  D   98\n",
      "0       www.google.com\n",
      "1       www.runoob.com\n",
      "2       www.taobao.com\n",
      "3    www.wikipedia.org\n",
      "4                    Q\n",
      "5                    D\n",
      "Name: site, dtype: object\n",
      "   Unnamed: 0    name            site  age\n",
      "0           0  Google  www.google.com   90\n",
      "   Unnamed: 0 name site  age\n",
      "5           5   Di    D   98\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 6 entries, 0 to 5\n",
      "Data columns (total 4 columns):\n",
      " #   Column      Non-Null Count  Dtype \n",
      "---  ------      --------------  ----- \n",
      " 0   Unnamed: 0  6 non-null      int64 \n",
      " 1   name        6 non-null      object\n",
      " 2   site        6 non-null      object\n",
      " 3   age         6 non-null      int64 \n",
      "dtypes: int64(2), object(2)\n",
      "memory usage: 320.0+ bytes\n",
      "None\n",
      "Unnamed: 0                 0\n",
      "name                  Google\n",
      "site          www.google.com\n",
      "age                       90\n",
      "Name: 0, dtype: object\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_csv('site.csv')\n",
    "print(df.to_string())\n",
    "print(df)\n",
    "print(df['site'])\n",
    "print(df.head(1))\n",
    "print(df.tail(1))\n",
    "print(df.info())\n",
    "print(df.loc[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### JSON"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     id  likes name             url\n",
      "0  A001     61  WSL  www.runoob.com\n",
      "1  A002    124  MLF  www.google.com\n",
      "2  A003     45   AJ  www.taobao.com\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_json('sites.json')\n",
    "print(df.to_string())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     id  likes    name             url\n",
      "0  A001     61    菜鸟教程  www.runoob.com\n",
      "1  A002    124  Google  www.google.com\n",
      "2  A003     45      淘宝  www.taobao.com\n"
     ]
    }
   ],
   "source": [
    "data =[\n",
    "    {\n",
    "      \"id\": \"A001\",\n",
    "      \"name\": \"菜鸟教程\",\n",
    "      \"url\": \"www.runoob.com\",\n",
    "      \"likes\": 61\n",
    "    },\n",
    "    {\n",
    "      \"id\": \"A002\",\n",
    "      \"name\": \"Google\",\n",
    "      \"url\": \"www.google.com\",\n",
    "      \"likes\": 124\n",
    "    },\n",
    "    {\n",
    "      \"id\": \"A003\",\n",
    "      \"name\": \"淘宝\",\n",
    "      \"url\": \"www.taobao.com\",\n",
    "      \"likes\": 45\n",
    "    }\n",
    "]\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = pd.Series(['C ', ' Python', 'java', 'go','javascript','11',np.nan])\n",
    "print(s.str.lower())\n",
    "print(s.str.len())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      col1 col2\n",
      "row1     1    x\n",
      "row2     2    y\n",
      "row3     3    z\n"
     ]
    }
   ],
   "source": [
    "s = { \"col1\":{\"row1\":1,\"row2\":2,\"row3\":3},\n",
    "      \"col2\":{\"row1\":\"x\",\"row2\":\"y\",\"row3\":\"z\"}}\n",
    "                                                                                         \n",
    "df = pd.DataFrame(s)\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     id  likes    name             url\n",
      "0  A001     61    菜鸟教程  www.runoob.com\n",
      "1  A002    124  Google  www.google.com\n",
      "2  A003     45      淘宝  www.taobao.com\n"
     ]
    }
   ],
   "source": [
    "URL = 'https://static.runoob.com/download/sites.json'\n",
    "df = pd.read_json(URL)\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     id   name  math  physics  chemistry\n",
      "0  A001    Tom    60       66         61\n",
      "1  A002  James    89       76         51\n",
      "2  A003  Jenny    79       90         78\n",
      "     id   name  math  physics  chemistry         school_name   class\n",
      "0  A001    Tom    60       66         61  ABC primary school  Year 1\n",
      "1  A002  James    89       76         51  ABC primary school  Year 1\n",
      "2  A003  Jenny    79       90         78  ABC primary school  Year 1\n"
     ]
    }
   ],
   "source": [
    "with open('nested_list.json','r') as f:\n",
    "    data = json.loads(f.read())\n",
    "\n",
    "df_nested_list = pd.json_normalize(data, record_path =['students'])\n",
    "print(df_nested_list)\n",
    "\n",
    "df_nested_list = pd.json_normalize(data,record_path =['students'],meta=['school_name', 'class'])\n",
    "print(df_nested_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'school_name': 'ABC primary school', 'class': 'Year 1', 'info': {'president': 'John Kasich', 'address': 'ABC road, London, UK', 'contacts': {'email': 'admin@e.com', 'tel': '123456789'}}, 'students': [{'id': 'A001', 'name': 'Tom', 'math': 60, 'physics': 66, 'chemistry': 61}, {'id': 'A002', 'name': 'James', 'math': 89, 'physics': 76, 'chemistry': 51}, {'id': 'A003', 'name': 'Jenny', 'math': 79, 'physics': 90, 'chemistry': 78}]}\n",
      "     id   name  math  physics  chemistry   class info.president  \\\n",
      "0  A001    Tom    60       66         61  Year 1    John Kasich   \n",
      "1  A002  James    89       76         51  Year 1    John Kasich   \n",
      "2  A003  Jenny    79       90         78  Year 1    John Kasich   \n",
      "\n",
      "  info.contacts.tel  \n",
      "0         123456789  \n",
      "1         123456789  \n",
      "2         123456789  \n"
     ]
    }
   ],
   "source": [
    "import demjson\n",
    "\n",
    "\n",
    "data = '{\"school_name\":\"ABC primary school\",\"class\":\"Year 1\",\"info\":{\"president\":\"John Kasich\",\"address\":\"ABC road, London, UK\",\"contacts\":{\"email\":\"admin@e.com\",\"tel\":\"123456789\"}},\"students\":[{\"id\":\"A001\",\"name\":\"Tom\",\"math\":60,\"physics\":66,\"chemistry\":61},{\"id\":\"A002\",\"name\":\"James\",\"math\":89,\"physics\":76,\"chemistry\":51},{\"id\":\"A003\",\"name\":\"Jenny\",\"math\":79,\"physics\":90,\"chemistry\":78}]}'\n",
    "data = demjson.decode(data)\n",
    "print(data)\n",
    "\n",
    "df = pd.json_normalize(\n",
    "    data,\n",
    "    record_path =['students'],\n",
    "    meta=[\n",
    "        'class',\n",
    "        ['info', 'president'],\n",
    "        ['info', 'contacts', 'tel']\n",
    "    ]\n",
    ")\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          school_name   class  \\\n",
      "0  ABC primary school  Year 1   \n",
      "1  ABC primary school  Year 1   \n",
      "2  ABC primary school  Year 1   \n",
      "\n",
      "                                            students  \n",
      "0  {'id': 'A001', 'name': 'Tom', 'grade': {'math'...  \n",
      "1  {'id': 'A002', 'name': 'James', 'grade': {'mat...  \n",
      "2  {'id': 'A003', 'name': 'Jenny', 'grade': {'mat...  \n",
      "0    60\n",
      "1    89\n",
      "2    79\n",
      "Name: students, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "from glom import glom\n",
    "data = '{\"school_name\":\"ABC primary school\",\"class\":\"Year 1\",\"students\":[{\"id\":\"A001\",\"name\":\"Tom\",\"grade\":{\"math\":60,\"physics\":66,\"chemistry\":61}},{\"id\":\"A002\",\"name\":\"James\",\"grade\":{\"math\":89,\"physics\":76,\"chemistry\":51}},{\"id\":\"A003\",\"name\":\"Jenny\",\"grade\":{\"math\":79,\"physics\":90,\"chemistry\":78}}]}'\n",
    "data = demjson.decode(data)\n",
    "df = pd.DataFrame(data)\n",
    "print(df)\n",
    "\n",
    "df = df['students'].apply(lambda row: glom(row, 'grade.math'))\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 数据清洗  去空值  替换空值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0      3\n",
      "1      3\n",
      "2    NaN\n",
      "3      1\n",
      "4      3\n",
      "5    NaN\n",
      "6      2\n",
      "7      1\n",
      "8     na\n",
      "Name: NUM_BEDROOMS, dtype: object\n",
      "0    False\n",
      "1    False\n",
      "2     True\n",
      "3    False\n",
      "4    False\n",
      "5     True\n",
      "6    False\n",
      "7    False\n",
      "8    False\n",
      "Name: NUM_BEDROOMS, dtype: bool\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_csv('property-data.csv')\n",
    "\n",
    "print (df['NUM_BEDROOMS'])\n",
    "print (df['NUM_BEDROOMS'].isnull())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    3.0\n",
      "1    3.0\n",
      "2    NaN\n",
      "3    1.0\n",
      "4    3.0\n",
      "5    NaN\n",
      "6    2.0\n",
      "7    1.0\n",
      "8    NaN\n",
      "Name: NUM_BEDROOMS, dtype: float64\n",
      "0    False\n",
      "1    False\n",
      "2     True\n",
      "3    False\n",
      "4    False\n",
      "5     True\n",
      "6    False\n",
      "7    False\n",
      "8     True\n",
      "Name: NUM_BEDROOMS, dtype: bool\n"
     ]
    }
   ],
   "source": [
    "missing_values = [\"n/a\", \"na\", \"--\"]\n",
    "df = pd.read_csv('property-data.csv', na_values = missing_values)\n",
    "\n",
    "print (df['NUM_BEDROOMS'])\n",
    "print (df['NUM_BEDROOMS'].isnull())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           PID  ST_NUM ST_NAME OWN_OCCUPIED  NUM_BEDROOMS NUM_BATH   SQ_FT\n",
      "0  100001000.0   104.0  PUTNAM            Y           3.0        1  1000.0\n"
     ]
    }
   ],
   "source": [
    "new_df = df.dropna()      # 删除null\n",
    "print(new_df.to_string())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           PID  ST_NUM    ST_NAME OWN_OCCUPIED  NUM_BEDROOMS NUM_BATH    SQ_FT\n",
      "0  100001000.0   104.0     PUTNAM            Y           3.0        1   1000.0\n",
      "1  100002000.0   197.0  LEXINGTON            N           3.0      1.5  12345.0\n",
      "3  100004000.0   201.0   BERKELEY           12           1.0    12345    700.0\n",
      "4      12345.0   203.0   BERKELEY            Y           3.0        2   1600.0\n",
      "5  100006000.0   207.0   BERKELEY            Y       12345.0        1    800.0\n",
      "7  100008000.0   213.0    TREMONT            Y           1.0        1  12345.0\n",
      "8  100009000.0   215.0    TREMONT            Y       12345.0        2   1800.0\n"
     ]
    }
   ],
   "source": [
    "df.dropna(subset=['ST_NUM'])\n",
    "print(df.to_string())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           PID  ST_NUM    ST_NAME OWN_OCCUPIED  NUM_BEDROOMS NUM_BATH    SQ_FT\n",
      "0  100001000.0   104.0     PUTNAM            Y           3.0        1   1000.0\n",
      "1  100002000.0   197.0  LEXINGTON            N           3.0      1.5  12345.0\n",
      "3  100004000.0   201.0   BERKELEY           12           1.0    12345    700.0\n",
      "4      12345.0   203.0   BERKELEY            Y           3.0        2   1600.0\n",
      "5  100006000.0   207.0   BERKELEY            Y       12345.0        1    800.0\n",
      "7  100008000.0   213.0    TREMONT            Y           1.0        1  12345.0\n",
      "8  100009000.0   215.0    TREMONT            Y       12345.0        2   1800.0\n"
     ]
    }
   ],
   "source": [
    "df.fillna(12345)        # 填充\n",
    "print(df.to_string())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           PID      ST_NUM     ST_NAME OWN_OCCUPIED NUM_BEDROOMS NUM_BATH SQ_FT\n",
      "0  100001000.0  104.000000      PUTNAM            Y            3        1  1000\n",
      "1  100002000.0  197.000000   LEXINGTON            N            3      1.5    --\n",
      "2  100003000.0  191.428571   LEXINGTON            N          NaN        1   850\n",
      "3  100004000.0  201.000000    BERKELEY           12            1      NaN   700\n",
      "4          NaN  203.000000    BERKELEY            Y            3        2  1600\n",
      "5  100006000.0  207.000000    BERKELEY            Y          NaN        1   800\n",
      "6  100007000.0  191.428571  WASHINGTON          NaN            2   HURLEY   950\n",
      "7  100008000.0  213.000000     TREMONT            Y            1        1   NaN\n",
      "8  100009000.0  215.000000     TREMONT            Y           na        2  1800\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_csv('property-data.csv')\n",
    "x = df[\"ST_NUM\"].mean()\n",
    "df[\"ST_NUM\"].fillna(x, inplace = True)\n",
    "\n",
    "print(df.to_string())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 格式错误 数据错误"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           Date  duration\n",
      "day1 2020-12-01        50\n",
      "day2 2020-12-02        40\n",
      "day3 2020-12-26        45\n"
     ]
    }
   ],
   "source": [
    "# 第三个日期格式错误\n",
    "data = {\n",
    "  \"Date\": ['2020/12/01', '2020/12/02' , '20201226'],\n",
    "  \"duration\": [50, 40, 45]\n",
    "}\n",
    "df = pd.DataFrame(data, index = [\"day1\", \"day2\", \"day3\"])\n",
    "df['Date'] = pd.to_datetime(df['Date'])\n",
    "\n",
    "print(df.to_string())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     name  age\n",
      "0  Google   50\n",
      "1  Runoob   40\n",
      "2  Taobao   30\n",
      "     name  age\n",
      "0  Google  120\n",
      "1  Runoob  120\n",
      "2  Taobao  120\n"
     ]
    }
   ],
   "source": [
    "person = {\n",
    "  \"name\": ['Google', 'Runoob' , 'Taobao'],\n",
    "  \"age\": [50, 40, 12345]    # 12345 年龄数据是错误的\n",
    "}\n",
    "df = pd.DataFrame(person)\n",
    "df.loc[2, 'age'] = 30    # 修改\n",
    "\n",
    "print(df.to_string())\n",
    "\n",
    "for x in df.index:\n",
    "    if df.loc[x, \"age\"] > 20:\n",
    "        df.loc[x, \"age\"] = 120\n",
    "\n",
    "print(df.to_string())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     name  age\n",
      "0  Google   50\n",
      "1  Runoob   40\n"
     ]
    }
   ],
   "source": [
    "for x in df.index:\n",
    "    if df.loc[x, \"age\"] > 20:\n",
    "        new=df.drop(x)  # 删除\n",
    "\n",
    "print(new.to_string())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    False\n",
      "1    False\n",
      "2    False\n",
      "dtype: bool\n"
     ]
    }
   ],
   "source": [
    "print(df.duplicated())  # 去重\n",
    "print(df.drop_duplicates())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Demo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     id  name             url  math  english\n",
      "0  A001  None  www.runoob.com    60       60\n",
      "1  A002   MLF  www.google.com    70       70\n",
      "2  A003    AJ  www.taobao.com    10       10\n",
      "3  A003  None  www.taobao.com    20       20\n",
      "     math\n",
      "姓名       \n",
      "MLF    70\n",
      "       math\n",
      "count   1.0\n",
      "mean   70.0\n",
      "std     NaN\n",
      "min    70.0\n",
      "25%    70.0\n",
      "50%    70.0\n",
      "75%    70.0\n",
      "max    70.0\n",
      "math    70.0\n",
      "dtype: float64\n",
      "math    70.0\n",
      "dtype: float64\n",
      "   math\n",
      "0    70\n",
      "math    1\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_json('sites.json')\n",
    "print(df.to_string())\n",
    "# df=df.dropna()\n",
    "df=df.fillna({'name':'AAA'})\n",
    "df = df.rename(columns={'name': '姓名', 'url': '地址'})\n",
    "df = df.groupby('姓名').agg({'math': 'mean', 'english': 'median'})\n",
    "df = df.loc[df['math'] >= 60, ['math']]\n",
    "print(df)\n",
    "print(df.describe())\n",
    "print(df.mean())\n",
    "print(df.median())\n",
    "print(df.mode())\n",
    "print(df.count())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'datetime' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-167-b95a162f46fd>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdatetime\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mnow\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      2\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mTimestamp\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'2020-04-24 00:14:15'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mTimestamp\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m1587687255\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0munit\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m's'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'datetime' is not defined"
     ]
    }
   ],
   "source": [
    "from datetime import datetime\n",
    "print(datetime.now())\n",
    "\n",
    "print(pd.Timestamp('2020-04-24 00:14:15'))\n",
    "print(pd.Timestamp(1587687255, unit='s'))\n",
    "\n",
    "print(pd.date_range(\"9:00\", \"18:10\", freq=\"30min\").time)\n",
    "print(pd.date_range(\"9:00\", \"18:10\", freq=\"H\").time)\n",
    "\n",
    "print(pd.to_datetime(pd.Series(['Jun 3, 2020', '2020-12-10', None])))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
